begin;

create sequence cooc_commercial_id_seq increment by 1;
create sequence cooi_item_id_seq increment by 1;

create table certificate_of_origin_commercial
(
commercial_id integer default nextval('cooc_commercial_id_seq'),
buyer_id integer,
account_id integer,
pre_carriage character varying,
place_of_receipt character varying,
ship_mode_id integer,
ship_from_city integer,
port_dis integer,
port_des integer,
marks_and_nos character varying,
no_of_pkgs character varying,
description character varying,
quantity_type_id character varying,
"value" character varying,
name_of_signatory character varying
);
/*
alter table _jbuyer add CONSTRAINT jb_buyer_id_pk primary Key(buyer_jid);
alter table _jaccount add CONSTRAINT ja_account_jid_pk primary Key(account_jid);
*/
/*
alter table _mship_mode add CONSTRAINT ms_ship_mode_jid_pk primary Key(shipment_mode_id);
alter table _mcity add CONSTRAINT mc_city_id_pk primary Key(city_id);
alter table _mport add CONSTRAINT mc_port_id_pk primary Key(port_id);

alter table certificate_of_origin_commercial ADD CONSTRAINT
cooc_commercial_id_pk primary key(commercial_id);

alter table certificate_of_origin_commercial ADD CONSTRAINT
cooc_buyer_id_fk foreign key(buyer_id) references _jbuyer(buyer_jid);

alter table certificate_of_origin_commercial ADD CONSTRAINT
cooc_account_id_fk foreign key(account_id) references _jaccount(account_jid);

alter table certificate_of_origin_commercial ADD CONSTRAINT
cooc_ship_mode_id_fk foreign key(ship_mode_id) references _mship_mode(shipment_mode_id);

alter table certificate_of_origin_commercial ADD CONSTRAINT
cooc_city_id_fk foreign key(ship_from_city) references _mcity(city_id);

alter table certificate_of_origin_commercial ADD CONSTRAINT
cooc_port_dis_fk foreign key(port_dis) references _mport(port_id);

alter table certificate_of_origin_commercial ADD CONSTRAINT
cooc_port_des_fk foreign key(port_des) references _mport(port_id);
*/

create table certificate_of_origin_items
(
item_id integer default nextval('cooi_item_id_seq'),
commercial_id integer,
item_no character varying,
item_desc character varying,
remarks character varying,
qty character varying,
val character varying
);
/*
alter table certificate_of_origin_items ADD CONSTRAINT
cooi_commercial_id_pk primary key(item_id);

alter table certificate_of_origin_items ADD CONSTRAINT
cooi_commercial_id_fk foreign key(commercial_id) references certificate_of_origin_commercial(commercial_id);
*/

alter table certificate_of_origin_commercial add column
total_val numeric;

alter table certificate_of_origin_commercial add column
total_qty numeric;


commit;

